package com.sjtucook;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.*;

public class DBAdapter {
	private static final String DB_NAME = "cook.db";
	private static int DB_VERSION = 1;
	// tables in db
	public static String DB_TABLE_DISH = "DISH";
	public static String DB_TABLE_SORT = "SORT_DOMAIN";
	public static String DB_TABLE_HIS = "HISTORY";
	public static String DB_VIEW_HISVIEW = "HISVIEW";
	// key in DB_TABLE_SORT and DB_TABLE_DISH
	public static String KEY_ID = "_id";
	public static String KEY_NAME = "name";
	public static String KEY_MATERIAL = "material";
	public static String KEY_DESCRIPTION = "description";
	public static String KEY_IMAGE = "image";
	public static String KEY_RATE = "rate";
	public static String KEY_FAVORITE = "favorite";
	public static String KEY_DATE = "date";
	// key in DB_TABLE_DORT to specify the table name of the sorting category
	// and column name in DB_TABLE_DISH
	public static String KEY_TABLE_NAME = "table_name";
	public static String KEY_COLUMN_NAME = "column_name";

	private SQLiteDatabase mydb = null;
	private DatabaseHelper myhelper = null;
	private Context myContext = null;

	// 构造函数-取得Context
	public DBAdapter(Context context) {

		myContext = context;
	}

	// 打开数据库，返回数据库对象
	public void open() throws SQLException {

		myhelper = new DatabaseHelper(myContext);
		mydb = myhelper.getWritableDatabase();
	}

	// 关闭数据库
	public void close() {

		myhelper.close();
	}

	public ArrayList<HashMap<String, Object>> queryFirstSort() {
		Cursor cursor = this.mydb.query(DBAdapter.DB_TABLE_SORT, null, null,
				null, null, null, null);
		ArrayList<HashMap<String, Object>> sourceList = new ArrayList<HashMap<String, Object>>();
		HashMap<String, Object> tempHashMap = new HashMap<String, Object>();
		int index = 0;
		for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
			index = cursor.getColumnIndex(DBAdapter.KEY_IMAGE);
			tempHashMap.put(DBAdapter.KEY_IMAGE, R.drawable.icon);
			index = cursor.getColumnIndex(DBAdapter.KEY_NAME);
			tempHashMap.put(DBAdapter.KEY_NAME, cursor.getString(index));
			index = cursor.getColumnIndex(DBAdapter.KEY_COLUMN_NAME);
			tempHashMap.put(DBAdapter.KEY_COLUMN_NAME, cursor.getString(index));
			index = cursor.getColumnIndex(DBAdapter.KEY_TABLE_NAME);
			tempHashMap.put(DBAdapter.KEY_TABLE_NAME, cursor.getString(index));
			index = cursor.getColumnIndex(DBAdapter.KEY_DESCRIPTION);
			tempHashMap.put(DBAdapter.KEY_DESCRIPTION, cursor.getString(index));
			sourceList.add(tempHashMap);
			tempHashMap = new HashMap<String, Object>();
		}
		return sourceList;
	}

	public ArrayList<HashMap<String, Object>> querySecondSort(String table_name) {
		Cursor cursor = this.mydb.query(table_name, null, null, null, null,
				null, null);
		ArrayList<HashMap<String, Object>> sourceList = new ArrayList<HashMap<String, Object>>();
		HashMap<String, Object> tempHashMap = new HashMap<String, Object>();
		int index = 0;
		for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
			index = cursor.getColumnIndex(DBAdapter.KEY_IMAGE);
			tempHashMap.put(DBAdapter.KEY_IMAGE, R.drawable.icon);
			index = cursor.getColumnIndex(DBAdapter.KEY_NAME);
			tempHashMap.put(DBAdapter.KEY_NAME, cursor.getString(index));
			index = cursor.getColumnIndex(DBAdapter.KEY_ID);
			tempHashMap.put(DBAdapter.KEY_ID, cursor.getString(index));
			index = cursor.getColumnIndex(DBAdapter.KEY_DESCRIPTION);
			tempHashMap.put(DBAdapter.KEY_DESCRIPTION, cursor.getString(index));
			sourceList.add(tempHashMap);
			tempHashMap = new HashMap<String, Object>();
		}
		return sourceList;
	}

	public Cursor queryDish(String column_name, String id) {
		Cursor cursor = this.mydb.query(DBAdapter.DB_TABLE_DISH, null,
				column_name + " = " + id, null, null, null, null);
		return cursor;
	}

	public Cursor queryTop() {
		Cursor cursor = this.mydb.query(DBAdapter.DB_TABLE_DISH, null, null,
				null, null, null, DBAdapter.KEY_RATE + " DESC", "10");
		return cursor;
	}

	public Cursor queryFavorite() {
		Cursor cursor = this.mydb.query(DBAdapter.DB_TABLE_DISH, null,
				DBAdapter.KEY_FAVORITE + " = 1", null, null, null, null);
		return cursor;
	}

	public Cursor queryHistory() {
		Cursor cursor = this.mydb.query(DBAdapter.DB_VIEW_HISVIEW, null, null,
				null, null, null, null);
		return cursor;
	}

	public Cursor searchDish(String query) {
		Cursor cursor = this.mydb.query(DBAdapter.DB_TABLE_DISH, null,
				DBAdapter.KEY_NAME + " LIKE \"%" + query + "%\"", null, null, null,
				null);
		return cursor;
	}

	public void updateHistory(Dish dish) {
		Date date = new Date();
		Cursor cursor = this.mydb
				.query(DBAdapter.DB_TABLE_HIS, null, DBAdapter.KEY_ID + " = "
						+ dish.getId(), null, null, null, null);
		ContentValues cv = new ContentValues();
		cv.put(DBAdapter.KEY_ID, dish.getId());
		cv.put(DBAdapter.KEY_DATE,
				new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date));
		if (cursor.getCount() == 0) {
			this.mydb.insert(DB_TABLE_HIS, null, cv);
			
		} else {
			this.mydb.update(DB_TABLE_HIS, cv,
					DBAdapter.KEY_ID + " = " + dish.getId(), null);
		}
	}

	public void updateDish(Dish dish) {
		ContentValues cv = new ContentValues();
		cv.put(DBAdapter.KEY_RATE, dish.getRate());
		cv.put(DBAdapter.KEY_FAVORITE, dish.getFavorite());
		this.mydb.update(DB_TABLE_DISH, cv,
				DBAdapter.KEY_ID + " = " + dish.getId(), null);
	}

	private static class DatabaseHelper extends SQLiteOpenHelper {

		// 构造函数-创建一个数据库
		DatabaseHelper(Context context) {

			// 当调用getWritableDatabase()
			// 或 getReadableDatabase()方法时
			// 则创建一个数据库
			super(context, DB_NAME, null, DB_VERSION);
		}

		// 创建一个数据库
		@Override
		public void onCreate(SQLiteDatabase db) {

			// 数据库没有表时创建一个
			// db.execSQL(DB_CREATE);
		}

		// 升级数据库
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

			// DB_VERSION = newVersion;
			// db.execSQL("DROP TABLE IF EXISTS " + DB_TABLE);
			// onCreate(db);
		}
	}

}
